package org.jeecg.modules.bpm.util;

import org.jeecg.common.constant.DataBaseConstant;

import java.text.MessageFormat;

public class SqlUtil {
	/**
	 * 分页SQL
	 */
	public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}"; // mysql
	public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";// postgresql
	public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; // oracle
	public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}"; // sqlserver



	/**
	 * 按照数据库类型，封装SQL
	 * 
	 * @param dbType
	 *            数据库类型
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public static String createPageSql(String dbType, String sql, int pageNo, int pageSize) {
		int beginNum = (pageNo - 1) * pageSize;
		String[] sqlParam = new String[3];
		sqlParam[0] = sql;
		sqlParam[1] = beginNum + "";
		sqlParam[2] = pageSize + "";
		if (dbType == null || "".equals(dbType)) {
			throw new RuntimeException("SqlUtil:(数据库类型:dbType)没有设置,请检查配置文件");
		}

		if (DataBaseConstant.DB_TYPE_MYSQL.equalsIgnoreCase(dbType)) {
			sql = MessageFormat.format(MYSQL_SQL, sqlParam);
		} else if (DataBaseConstant.DB_TYPE_POSTGRESQL.equalsIgnoreCase(dbType)) {
			sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
		} else {
			int beginIndex = (pageNo - 1) * pageSize;
			int endIndex = beginIndex + pageSize;
			sqlParam[2] = Integer.toString(beginIndex);
			sqlParam[1] = Integer.toString(endIndex);
			if (DataBaseConstant.DB_TYPE_ORACLE.equalsIgnoreCase(dbType)) {
				sql = MessageFormat.format(ORACLE_SQL, sqlParam);
			} else if (DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(dbType)) {
				sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
				sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
			}
		}
		return sql;
	}
	
	
	/**
	 * 
	 * @param sql
	 * @return
	 */
	private static int getAfterSelectInsertPoint(String sql) {
		int selectIndex = sql.toLowerCase().indexOf("select");
		int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
		return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
	}

}
